دالة AGGREGATE في Excel

دوال الرياضيات والإحصاء

الغرض من الدالة

تُعد دالة AGGREGATE دالة قوية ومرنة يمكنها تنفيذ مجموعة واسعة من العمليات التجميعية (مثل الجمع، العد، المتوسط، الحد الأقصى، الحد الأدنى، إلخ) على نطاق من البيانات. ما يميزها هو قدرتها على تجاهل أنواع معينة من القيم أو الخلايا، مثل الصفوف المخفية، قيم الأخطاء، أو الدوال التجميعية المتداخلة، مما يوفر تحكماً دقيقاً في الحسابات.

صيغة الدالة (Syntax)

AGGREGATE(function_num, options, ref1, [ref2], ...) أو AGGREGATE(function_num, options, array, [k])

function_num: رقم يحدد الدالة التجميعية المراد تطبيقها (مثال: 1 لـ AVERAGE، 2 لـ COUNT، 9 لـ SUM..). ستظهر قائمة العمليات وأرقامها عند وضع مؤشر الفأرة عند رقم العملية (مطلوب).
options: رقم يحدد ما يجب تجاهله في الحساب. (مطلوب).
- 0: أو عدم وجود قيمة, تجاهل دوال SUBTOTAL و AGGREGATE المتداخلة.
- 1: تجاهل الصفوف المخفية ودوال SUBTOTAL و AGGREGATE المتداخلة.
- 2: تجاهل قيم الأخطاء ودوال SUBTOTAL و AGGREGATE المتداخلة.
- 3: تجاهل الصفوف المخفية وقيم الأخطاء ودوال SUBTOTAL و AGGREGATE المتداخلة.
- 4: لا شيء.
- 5: تجاهل الصفوف المخفية.
- 6: تجاهل قيم الأخطاء.
- 7: تجاهل الصفوف المخفية وقيم الأخطاء.
ref1: نطاق/نطاقات البيانات (أو المصفوفة) التي ستُطبق عليها الدالة التجميعية (مثل SUM، COUNT..) (مطلوب).
[ref2]: مطلوبة فقط لبعض دوال AGGREGATE التي تتطلب وسيطة ثانية. هي تمثل k عند استخدام دوال مثل LARGE أو SMALL. وتمثل quart أو k: عند استخدام دوال مثل PERCENTILE (لإيجاد النسبة المئوية) أو QUARTILE (لإيجاد الربيع).

الدوال التي تحتاج ref2

LARGE(array,k)
SMALL(array,k)
PERCENTILE.INC(array,k)
QUARTILE.INC(array,quart)
PERCENTILE.EXC(array,k)
QUARTILE.EXC(array,quart)
array: مصفوفة أو نطاق من البيانات (يُستخدم مع دوال مثل LARGE و SMALL). (مطلوب لبعض الدوال).
k: الوسيطة الثانية لبعض الدوال (مثل k لـ LARGE و SMALL). (اختياري، مطلوب لبعض الدوال).

الدوال التي تحتاج ref2
LARGE(array,k)
SMALL(array,k)
PERCENTILE.INC(array,k)
QUARTILE.INC(array,quart)
PERCENTILE.EXC(array,k)
QUARTILE.EXC(array,quart)

أمثلة عملية

المثال 1: جمع الخلايا المرئية وتجاهل الأخطاء

لدينا قائمة أرقام قد تحتوي على أخطاء، وبعض الصفوف قد تكون مخفية. نريد جمع الأرقام المرئية فقط مع تجاهل أي أخطاء.

	A
1	القيم
2	10
3	#DIV/0!
4	20
5	(صف مخفي)
6	30
7	المجموع (مرئي، بدون أخطاء)
8	60

الصيغة المستخدمة:

=AGGREGATE(9, 7, A2:A6)

توضيح: function_num = 9 يحدد دالة SUM. options = 7 يخبر الدالة بتجاهل الصفوف المخفية وقيم الأخطاء. إذا كانت الخلية A5 مخفية، فستكون النتيجة 10 + 20 + 30 = 60.

المثال 2: إيجاد ثاني أكبر قيمة مع تجاهل الصفوف المخفية

نريد إيجاد ثاني أكبر درجة في قائمة درجات الطلاب، مع العلم أن بعض الدرجات قد تكون في صفوف مخفية ولا نريد تضمينها في الحساب.

	A
1	الدرجات
2	95
3	88
4	(صف مخفي) 92
5	78
6	85
7	ثاني أكبر درجة (مرئية)
8	88

الصيغة المستخدمة:

=AGGREGATE(14, 5, A2:A6, 2)

توضيح: function_num = 14 يحدد دالة LARGE. options = 5 يخبر الدالة بتجاهل الصفوف المخفية. k = 2 يحدد أننا نريد ثاني أكبر قيمة. إذا كان الصف 4 مخفياً، فإن الدالة ستنظر فقط إلى القيم المرئية (95, 88, 78, 85) وستكون ثاني أكبر قيمة هي 88.

المثال 3: إيجاد ثالث أصغر قيمة مع تجاهل الأخطاء والصفوف المخفية

لدينا قائمة من المبيعات اليومية قد تحتوي على أخطاء، وبعض الأيام قد تكون مخفية. نريد إيجاد ثالث أصغر قيمة مبيعات مرئية وصحيحة.

	A
1	المبيعات
2	150
3	#N/A
4	120
5	(صف مخفي) 90
6	180
7	110
8	ثالث أصغر قيمة (مرئية، بدون أخطاء)
9	150

الصيغة المستخدمة:

=AGGREGATE(15, 7, A2:A7, 3)

توضيح: function_num = 15 يحدد دالة SMALL. options = 7 يخبر الدالة بتجاهل الصفوف المخفية وقيم الأخطاء. ref1 = A2:A7 هو نطاق البيانات. ref2 = 3 يحدد أننا نريد ثالث أصغر قيمة. إذا كان الصف 5 مخفياً، فإن الدالة ستنظر فقط إلى القيم المرئية والصحيحة (150, 120, 180, 110) وستكون ثالث أصغر قيمة هي 150.